package com.siqi.hr.system.common.dao.mysql;

import com.siqi.hr.common.generator.IdGenerator;
import com.siqi.hr.common.utils.StringUtil;
import com.siqi.hr.common.utils.TableConstant;
import com.siqi.hr.system.common.authority.Sysdata;
import com.siqi.hr.system.common.chan.TSQLSelectStat;
import com.siqi.hr.system.common.enums.FieldEditPageEnum;
import com.siqi.hr.system.common.model.ColumnUsage;
import com.siqi.hr.system.common.sql.Condition;
import com.siqi.hr.system.common.sql.SField;
import com.siqi.hr.system.common.sql.STable;
import com.siqi.hr.system.common.dao.intf.CommonOperationDao;
import com.siqi.hr.system.common.utils.CollectionUtils;
import com.siqi.hr.system.common.utils.CustomUtils;
import com.siqi.hr.system.common.utils.DateUtil;
import com.siqi.hr.system.common.utils.TableDatas;
import com.siqi.hr.system.custom.entity.EntityRecord;
import com.siqi.hr.system.custom.entity.FieldRecord;
import com.siqi.hr.system.organization.entity.Employee;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * 处理公共操作方法通用dao实现层
 * @version 1.0.0, Sep 25, 2016
 * @author xiaoxiumei
 * @since JDK1.6.0_12, Spring3.0.2
 */
@Component("commonOperationDao")
public class CommonOperationDaoImpl implements CommonOperationDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public long queryForCount(TSQLSelectStat countSelectStat) {
		countSelectStat.init();
		System.out.println("countStat======="+countSelectStat.toSqlString());
		return jdbcTemplate.queryForObject(countSelectStat.toSqlString().toString(),Long.class);
	}

	@Override
	public TableDatas queryForList(TSQLSelectStat datasSelectStat) {
		return TableDatas.queryForList(jdbcTemplate, datasSelectStat);
	}

	@Override
	public Map<String, Object> queryViewDatasById(TSQLSelectStat datasSelectStat) {
		return TableDatas.queryViewDatasById(jdbcTemplate,datasSelectStat);
	}

	@Override
	public List<FieldRecord> getEditOrViewPageFields(int entityId, String roleId,FieldEditPageEnum pageEnum) {
		StringBuffer sql = new StringBuffer("SELECT f.id,f.name,f.inchinese,f.entity_id,f.nulldisable,f.share_field_id,f.restricter,f.ifcascade,f.read_only,f.value_unique FROM sys_custom_field AS f WHERE f.entity_id = ? and f."+pageEnum.name()+" = false ");
		sql.append(" and f.id NOT IN (select rf.field_id from sys_org_role_field rf where rf.field_id = f.id and rf.role_id = '"+ roleId +"' and rf.entity_id = "+entityId+" and rf.forbid_edit_charge = true)");
		sql.append(" order by f.position asc ");
		List<FieldRecord> fieldList = this.jdbcTemplate.query(sql.toString(), new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				FieldRecord field = new FieldRecord();
				field.setId(rs.getInt("id"));
				field.setName(rs.getString("name"));
				field.setInchinese(rs.getString("inchinese"));
				field.setEntityRecord(new EntityRecord(rs.getInt("entity_id")));
				field.setNulldisable(rs.getBoolean("nulldisable"));
				field.setShareFieldId(rs.getObject("share_field_id") == null ? 0 : rs.getInt("share_field_id"));
				field.setRestricter(rs.getObject("restricter") == null ? 0 : rs.getInt("restricter"));
				field.setIfcascade(rs.getObject("ifcascade") == null ? 0 : rs.getInt("ifcascade"));
				field.setReadOnly(rs.getBoolean("read_only"));
				field.setValueUnique(rs.getBoolean("value_unique"));
				return field;
			}
		}, new Object[] {entityId});
		return fieldList;
	}

	@Override
	public String addTData(final Map<String,Object> dataMap, EntityRecord entityRecord, Employee emp) throws Exception{
		STable table = new STable(STable.TABLE_HEAD + entityRecord.getTableCode());
		//id
		if(!dataMap.containsKey("id")){
			dataMap.put("id",IdGenerator.createId(entityRecord.getTableCode()));
		}
		if(entityRecord.getTableCode() != TableConstant.ROLE_TCODE){
			//创建时间
			if(!dataMap.containsKey("create_date_datetime")){
				dataMap.put("create_date_datetime", DateUtil.formatDate(new Date(),DateUtil.FULL_DATE_TIME_FORMAT));
			}
		}
		//停用标志
		if(!dataMap.containsKey("flag_bit")){
			dataMap.put("flag_bit",false);
		}
		if(entityRecord.getTableCode() != TableConstant.ROLE_TCODE && entityRecord.getTableCode() != TableConstant.DEPT_TCODE){
			//记录创建者
			if(!dataMap.containsKey("pk_t_3_1")){
				dataMap.put("pk_t_3_1",emp.getId());
			}
			//记录更新者
			if(!dataMap.containsKey("pk_t_3_2")){
				dataMap.put("pk_t_3_2",emp.getId());
			}else{
				String updateUser = dataMap.get("pk_t_3_2") == null ? "" : dataMap.get("pk_t_3_2").toString();
				if(StringUtils.isEmpty(updateUser)){
					dataMap.put("pk_t_3_2",emp.getId());
				}
			}
			//前端会传值
			if(entityRecord.getPermission()){
				//记录拥有者
				if(!dataMap.containsKey("pk_t_3_3")){
					dataMap.put("pk_t_3_3",emp.getId());
				}else{
					String user = dataMap.get("pk_t_3_3") == null ? "" : dataMap.get("pk_t_3_3").toString();
					if(StringUtils.isEmpty(user)){
						dataMap.put("pk_t_3_3",emp.getId());
					}
				}
				//部门
				if(!dataMap.containsKey("pk_t_2_1")){
					dataMap.put("pk_t_2_1",emp.getDept().getId());
				}else{
					String deptId = dataMap.get("pk_t_2_1") == null ? "" : dataMap.get("pk_t_2_1").toString();
					if(StringUtils.isEmpty(deptId)){
						dataMap.put("pk_t_2_1",emp.getDept().getId());
					}
				}
			}
		}

		StringBuilder sql = new StringBuilder();
		final int fieldSize = dataMap.size();
		StringBuilder paramMark = CollectionUtils.join(CollectionUtils.fillPreChar(fieldSize, Character.valueOf('?')));

		StringBuffer fields = new StringBuffer();
		for(String key : dataMap.keySet()){
			fields.append(key + ",");
		}
		fields.deleteCharAt(fields.lastIndexOf(","));

		sql.append(" insert into  ").append(table.getName()).append("(").append(fields.toString()).append(")").append(" values(").append(paramMark).append(")");
		String id = dataMap.get("id") == null ? "" : dataMap.get("id").toString().trim();
		jdbcTemplate.update(sql.toString(),new PreparedStatementSetter() {
			int i = 1;
			public void setValues(PreparedStatement ps) throws SQLException {
				for(String key : dataMap.keySet()){
					String val = dataMap.get(key) == null ? "" : dataMap.get(key).toString().trim();
					//System.out.println("key==="+key+"  &val===="+val+"  &i==="+i);
					if(StringUtils.isEmpty(val)){
						ps.setObject(i, null);
					}else{
                        if(key.endsWith("_bit")){
							ps.setBoolean(i, val.equals("1") ? true : false);
						}else if(key.endsWith("_decimal") || key.endsWith("_precent") || key.endsWith("_currency")){
							ps.setInt(i,Integer.valueOf(val));
						}else{
							ps.setString(i, val);
						}
					}
					i++;
				}
			}
		});
		return id;
	}

	@Override
	public int updateTData(final Map<String,Object> dataMap, EntityRecord entityRecord, Employee emp, String id) throws Exception{
		STable table = new STable(STable.TABLE_HEAD + entityRecord.getTableCode());
		if(entityRecord.getTableCode() != TableConstant.ROLE_TCODE && entityRecord.getTableCode() != TableConstant.DEPT_TCODE) {
			//修改人
			if(!dataMap.containsKey("pk_t_3_2")){
				dataMap.put("pk_t_3_2",emp.getId());
			}
		}
		StringBuilder sql = new StringBuilder();
		final int fieldSize = dataMap.size();
		StringBuilder paramMark = CollectionUtils.join(CollectionUtils.fillPreChar(fieldSize, Character.valueOf('?')));

		StringBuffer fields = new StringBuffer();
		for(String key : dataMap.keySet()){
			fields.append(key + "= ?,");
		}
		fields.deleteCharAt(fields.lastIndexOf(","));

		sql.append(" update  ").append(table.getName()).append(" set " + fields.toString() + " where id='"+ id +"'");
		int numbers = jdbcTemplate.update(sql.toString(),new PreparedStatementSetter() {
			int i = 1;
			public void setValues(PreparedStatement ps) throws SQLException {
				for(String key : dataMap.keySet()){
					String val = dataMap.get(key) == null ? "" : dataMap.get(key).toString().trim();
					//System.out.println("update-----key==="+key+"  &val===="+val+"  &i==="+i);
					if(StringUtils.isEmpty(val)){
						ps.setObject(i, null);
					}else{
						if(key.endsWith("_bit")){
							ps.setBoolean(i, val.equals("1") ? true : false);
						}else if(key.endsWith("_decimal") || key.endsWith("_precent") || key.endsWith("_currency")){
							ps.setInt(i,Integer.valueOf(val));
						}else{
							ps.setString(i, val);
						}
					}
					i++;
				}
			}
		});
		return numbers;
	}

	@Override
	public int deleteTData(STable table, String id) throws Exception {
		StringBuilder sql = new StringBuilder();
		Condition condition = new Condition(new SField("id", table),Condition.ConditionType.eq, id);
		sql.append(" delete from ").append(table.getName()).append(" where ").append(condition.toSqlString());
		int counts = jdbcTemplate.update(sql.toString());
		return counts;
	}

	@Override
	public void updateValidData(STable table, String id) throws Exception {
		StringBuilder sql = new StringBuilder();
		Condition condition = new Condition(new SField("id", table),Condition.ConditionType.eq, id);
		sql.append(" update ").append(table.getName()).append(" set valid_data_bit = false ").append(" where ").append(condition.toSqlString());
		jdbcTemplate.execute(sql.toString());
	}

	@Override
	public Map<String, Object> getAllFieldsOfEntityExceptQuote(int entityId) {
		Map<String, Object> map = new HashMap<String, Object>();
        //定义存放非引用字段信息
		List<FieldRecord> mainFieldsList = new ArrayList<FieldRecord>();
        //定义存放引用字段信息
		Map<FieldRecord,List<FieldRecord>> deputyMap = new HashMap<FieldRecord,List<FieldRecord>>();
        //查询添加编辑页面显示的字段信息
		List<FieldRecord> mainFields = getAllFieldByEntity(entityId, false);
		if (mainFields.size() > 0) {
			for (int i = 0; i < mainFields.size(); i++) {
				if (mainFields.get(i).getName().startsWith("pk_t")) {
					List<FieldRecord> quoteFields = findEntityNorQuoteAndExportFields(CustomUtils.returnEntityId(mainFields.get(i).getName()),true,false);
					deputyMap.put(mainFields.get(i), quoteFields);
				} else {
					mainFieldsList.add(mainFields.get(i));
				}
			}
		}else {
			throw new RuntimeException("主实体的字段为空");
		}
		map.put("mainFields", mainFieldsList);
		map.put("map", deputyMap);
		return map;
	}

	@Override
	public List<FieldRecord> getAllFieldByEntity(Integer entityId, boolean ifcreatedate) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT id,name,entity_id,inchinese,list_show,quote_entity_id,nulldisable,value_unique from sys_custom_field as field ");
		sql.append("where field.entity_id=? and field.name!='id'");
		if (!ifcreatedate) {
			sql.append(" and field.hidden_edit = false");
		}
		List<FieldRecord> findList = new ArrayList<FieldRecord>();
		if (findList.size() == 0) {
			findList = this.jdbcTemplate.query(sql.toString(),new Object[] { entityId },new FieldRecord.ManyFieldRecord());
		}
		return findList;
	}

	@Override
	public List<FieldRecord> findEntityNorQuoteAndExportFields(int quote_entity_id, boolean queryQuoteFiled, boolean ifexport) {
		List<FieldRecord> resultList = new ArrayList<FieldRecord>();
		StringBuilder sql = new StringBuilder();
		if (queryQuoteFiled) {
			sql.append("select id,name,entity_id,inchinese,list_show,quote_entity_id from sys_custom_field ");
			sql.append("where entity_id =? and name not like 'pk%' and name!='id' ");
			if (ifexport){
				sql.append(" and name!='name'");
			}
		} else {
			sql.append("select id,name,entity_id,inchinese,list_show,quote_entity_id from sys_custom_field ");
			sql.append("where entity_id =? and name like 'pk%'");
		}
		if (resultList.size() == 0) {
			resultList = this.jdbcTemplate.query(sql.toString(),new Object[] { quote_entity_id },new FieldRecord.quoteFieldRecord());
		}
		return resultList;
	}

	@Override
	public boolean dataIsDisable(STable table, String id) {
		boolean bool = false;
		StringBuilder sql = new StringBuilder(" select flag_bit from " ).append(table.getName()).append(" where id = '").append(id).append("'");
		try {
			bool = jdbcTemplate.queryForObject(sql.toString(),Boolean.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return bool;
	}

	@Override
	public int disableTDate(STable table, String id) throws Exception {
		StringBuilder sql = new StringBuilder();
		Condition condition = new Condition(new SField("id", table),Condition.ConditionType.eq, id);
		sql.append(" update ").append(table.getName()).append(" set flag_bit = true ").append(" where ").append(condition.toSqlString());
		int counts = jdbcTemplate.update(sql.toString());
		return counts;
	}

	@Override
	public void enableTDate(STable table, String id) throws Exception {
		StringBuilder sql = new StringBuilder();
		Condition condition = new Condition(new SField("id", table),Condition.ConditionType.eq, id);
		sql.append(" update ").append(table.getName()).append(" set flag_bit = false ").append(" where ").append(condition.toSqlString());
		this.jdbcTemplate.execute(sql.toString());
	}

	@Override
	public Map<String, Object> getMapFields(String  pkTableName,int tableCode,String dataId) throws Exception {
		STable table = new STable(STable.TABLE_HEAD + tableCode);
		String pkTable = pkTableName.substring(3,pkTableName.lastIndexOf("_"));
		String sql ="select p.id as id , p.name_varchar as name from "+table+" as t left join "+pkTable+" as p on t."+pkTableName+"=p.id where t.id='"+dataId+"' ";
		Map<String, Object> map = jdbcTemplate.queryForMap(sql);
		return map;
	}

	@Override
	public JSONArray getAllQuoteTableInfo(String tableName) {
		JSONArray quoteArray = new JSONArray();
		StringBuffer sql = new StringBuffer("SELECT kcu.TABLE_NAME, kcu.COLUMN_NAME, kcu.REFERENCED_TABLE_NAME, kcu.REFERENCED_COLUMN_NAME, rc.UPDATE_RULE,");
		sql.append("rc.DELETE_RULE  FROM information_schema.REFERENTIAL_CONSTRAINTS as rc  LEFT JOIN information_schema.KEY_COLUMN_USAGE as kcu on ");
		sql.append(" rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME  WHERE rc.CONSTRAINT_SCHEMA = '").append(Sysdata.getInstance().getDatabase()).append("' and rc.UNIQUE_CONSTRAINT_SCHEMA = '");
		sql.append(Sysdata.getInstance().getDatabase()).append("' and rc.TABLE_NAME = kcu.TABLE_NAME and rc.REFERENCED_TABLE_NAME = kcu.REFERENCED_TABLE_NAME ");
		sql.append(" and rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA and rc.DELETE_RULE = 'RESTRICT' and rc.REFERENCED_TABLE_NAME = '"+tableName+"' ORDER BY kcu.REFERENCED_TABLE_NAME, kcu.TABLE_NAME");
		List<ColumnUsage> quoteList = jdbcTemplate.query(sql.toString(), new RowMapper(){
			public ColumnUsage mapRow(ResultSet rs, int rowNum) throws SQLException {
				ColumnUsage cu = new ColumnUsage();
				cu.setTableName(rs.getString("TABLE_NAME"));
				cu.setColumnName(rs.getString("COLUMN_NAME"));
				cu.setReferencedTableName(rs.getString("REFERENCED_TABLE_NAME"));
				cu.setReferencedColumnName(rs.getString("REFERENCED_COLUMN_NAME"));
				cu.setUpdateRule(rs.getString("UPDATE_RULE"));
				cu.setDeleteRule(rs.getString("DELETE_RULE"));
				return cu;
			}
		});
		for(ColumnUsage usage : quoteList){
			JSONObject obj = new JSONObject();
			obj.put("tableName",usage.getTableName());
			obj.put("columnName",usage.getColumnName());
			quoteArray.add(obj);
		}
		return quoteArray;
	}

	@Override
	public List<Map<String,Object>> isQuoteByOtherTable(JSONArray quoteArray, String id) {
		List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
		String splitStr = STable.TABLE_HEAD.toLowerCase();
		StringBuilder sql1 = new StringBuilder();
		StringBuilder sql2 = new StringBuilder();
		String s;
		for(int i = 0; i < quoteArray.size(); i++){
			JSONObject tableObj = quoteArray.getJSONObject(i);
			//获取引用该外键的表名
			String tableName = tableObj.get("tableName") == null ? "" : tableObj.getString("tableName");
			//获取引用该外键的表字段名
			String columnName = tableObj.get("columnName") == null ? "" : tableObj.getString("columnName");
			if (tableName.startsWith(splitStr)) {
				s = "select '" + tableName + "' tname, id, name_varchar as name from " + tableName + " where "+ columnName + " = '" + id + "' ";
				sql1.append(s + " union ");
			} else {
				s = "select '" + tableName + "' tname, id,name_varchar as name from " + tableName + " where "+ columnName + " = '" + id + "' ";
				sql2.append(s + " union ");
			}
		}
        //查询客户化实体表引用该字段的表信息
		if (!sql1.toString().equals("")) {
			try {
				sql1 = new StringBuilder(sql1.subSequence(0,sql1.lastIndexOf("union")));
				resultList = jdbcTemplate.queryForList(sql1.toString());
			} catch (DataAccessException e) {
				e.printStackTrace();
			} catch (NumberFormatException e) {
				e.printStackTrace();
			}
		}
		//查询系统实体表引用该字段的表信息
		if (!sql2.toString().equals("")) {
			try {
				sql2 = new StringBuilder(sql2.subSequence(0,sql2.lastIndexOf("union")));
				List<Map<String,Object>> reftable = jdbcTemplate.queryForList(sql2.toString());
				for(Map<String,Object> resultMap : reftable){
					resultList.add(resultMap);
				}
			} catch (DataAccessException e) {
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public int addZDate(String zTableName, final String host, final String related, final Boolean type) throws Exception {
		final String sql = "insert into "+zTableName+" (host_record_id,related_record_id,type) values(?,?,?) ";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update( new PreparedStatementCreator(){
			 @Override
			 public PreparedStatement createPreparedStatement(Connection conn) throws SQLException{
				 PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				 ps.setString(1, host);
				 ps.setString(2, related);
				 ps.setBoolean(3,type);
				 return ps;
			 }
		 },keyHolder);
		return keyHolder.getKey().intValue();
	}

	@Override
	public int deleteZDate(String zTableName, String host, String related){
		String sql = "delete from "+zTableName+" where 1=1 ";
		if(StringUtils.isNotBlank(host)){
			sql += " and host_record_id = '"+host+"' ";
		}
		if(StringUtils.isNotBlank(related)){
			sql += " and related_record_id in ("+ StringUtil.getString(related)+")";
		}
		return jdbcTemplate.update(sql);
	}

	@Override
	public int addEntityRelation(String tableName, String hostId, String relatedIds) {
		int [] counts = new int [0];
		String [] rIds = relatedIds.split(",");
		List<String> sqls = new ArrayList<String>();
		for(int i = 0; i < rIds.length; i++){
			String sql = "insert into "+tableName+"(host_record_id,related_record_id,type) values('"+hostId+"','"+rIds[i]+"',false)";
			sqls.add(sql);
		}
		if(sqls.size() > 0){
			this.deleteZDate(tableName,hostId,relatedIds);
			counts = jdbcTemplate.batchUpdate(sqls.toArray(new String [sqls.size()]));
		}
		return counts.length;
	}
}
